package lumis.service.rss;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;

import lumis.content.ContentManagerFactory;
import lumis.content.core.ContentVersion;
import lumis.portal.PortalException;
import lumis.portal.UnexpectedException;
import lumis.portal.dao.DaoException;
import lumis.portal.dao.hibernate.ITransactionHibernate;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.portal.group.GroupConfig;
import lumis.util.ITransaction;
import lumis.util.JdbcUtil;

/**
 * DAO JDBC implementation for RSS related data
 *
 * @version $Revision: 10551 $ $Date: 2009-06-30 19:03:39 -0300 (Tue, 30 Jun 2009) $
 * @since 4.1.0
 */
public class RssDao implements IRssDao
{
	public RssConfig get(String id, ITransaction transaction) throws PortalException
	{
		try
		{
			RssConfig loadedConfig = null;
			
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			
			PreparedStatement statement = connection.prepareStatement("SELECT " +
					"r.id, r.name, r.header, r.position, r.serviceId, r.servInstanceId, " +
					"r.source, r.title, r.description, r.link, r.pubDate, r.filter, " +
					"r.path, r.maxRows, lum_Content.serviceInstanceId FROM " +
					"lum_Rss r INNER JOIN " +
					"lum_ContentVersion ON r.id = lum_ContentVersion.itemId INNER JOIN " +
					"lum_ContentLocale ON lum_ContentVersion.contentLocaleId = lum_ContentLocale.id INNER JOIN " +
					"lum_Content ON lum_ContentLocale.contentId = lum_Content.id " +
					"WHERE r.id = ?");
			try
			{
				statement.setString(1, id);
				
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
					{
						loadedConfig = new RssConfig();
						loadedConfig.setId(resultSet.getString("id"));
						loadedConfig.setName(resultSet.getString("name"));
						
						// reading text from column 'header'.
						String header = (String)Hibernate.TEXT.nullSafeGet(resultSet, "header");
						loadedConfig.setHeader(header);
						
						loadedConfig.setPosition(resultSet.getInt("position"));
						loadedConfig.setServiceId(resultSet.getString("serviceId"));
						loadedConfig.setServInstanceId(resultSet.getString("servInstanceId"));
						loadedConfig.setSourceId(resultSet.getString("source"));
						loadedConfig.setTitleFieldId(resultSet.getString("title"));
						loadedConfig.setDescriptionFieldId(resultSet.getString("description"));
						loadedConfig.setLinkFieldId(resultSet.getString("link"));
						loadedConfig.setPubDateFieldId(resultSet.getString("pubDate"));
						loadedConfig.setRssServiceInstanceId(resultSet.getString("serviceInstanceId"));
						loadedConfig.setFilter(resultSet.getString("filter"));
						loadedConfig.setPath(resultSet.getString("path"));
						loadedConfig.setMaxRows(resultSet.getInt("maxRows"));
					}
				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();
			}
			
			ContentVersion contentVersion = ContentManagerFactory.getContentManager().getContentVersionByItemId(id);
			loadedConfig.setLocale(contentVersion.getContentLocale().getLocale().toString());

			return loadedConfig;
		}
		catch (DaoException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public Collection<String> getIdsByServiceInstanceId(String serviceInstanceId, ITransaction transaction) throws PortalException
	{
		try
		{			
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			
			PreparedStatement statement = connection.prepareStatement("SELECT id FROM lum_Rss WHERE servInstanceId = ?");
			try
			{
				statement.setString(1, serviceInstanceId);
				Collection<String> rssId = JdbcUtil.fillListString(statement, "id");

				return rssId;
			}
			finally
			{
				statement.close();
			}
		}
		catch (DaoException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Collection<RssResultRow> getResults(RssConfig rssConfig, ITransaction transaction) throws PortalException
	{
		Collection<RssResultRow> rssResults = new ArrayList<RssResultRow>();
		
		Session session = ((ITransactionHibernate)transaction).getSession();
		
		StringBuilder sbQuery = new StringBuilder(300);
		sbQuery.append("SELECT c.id as contentId, t."+rssConfig.getTitleFieldId()+" as title, " +
				"t."+rssConfig.getDescriptionFieldId()+" as description");
		
		if (rssConfig.getLinkFieldId() != null && rssConfig.getLinkFieldId().length() > 0)
			sbQuery.append( ", t."+rssConfig.getLinkFieldId()+" as link");
		
		if (rssConfig.getPubDateFieldId() != null && rssConfig.getPubDateFieldId().length() > 0)
			sbQuery.append( ", t."+rssConfig.getPubDateFieldId()+" as pubDate");
		else
			sbQuery.append( ", cp.publishStartDateTime as pubDate");
		
		sbQuery.append( ", cv.lastModifiedDateTime as lastModifiedDate");
			
		sbQuery.append( " FROM lum_Content c INNER JOIN " +
				"lum_ContentLocale cl ON cl.contentId = c.id INNER JOIN " +
				"lum_ContentVersion cv ON cl.publishedVersionId = cv.id INNER JOIN " +
				"lum_ContentPublication cp ON cp.contentVersionId = cv.id INNER JOIN " +
				rssConfig.getTableName() + " t ON t." + rssConfig.getPrimaryKeyFieldId() + " = cv.itemId " +
				"WHERE cp.published = 1 AND EXISTS(select * from " +
				"lum_ContentPubPrincipal cpPrinc, lum_ContentPubServiceInst cpSI " +
				"WHERE cp.id = cpPrinc.contentPublicationId AND cpPrinc.principalId = :principalId AND " +
				"cp.id = cpSI.contentPublicationId AND cpSI.serviceInstanceId = :serviceInstanceId) AND " +
				"(cl.locale = :locale or (cl.locale = c.defaultLocale and " +
				"not exists (select * from lum_ContentLocale l where l.contentId = c.id and l.locale = :locale)))");
		
		if (rssConfig.getFilter() != null && rssConfig.getFilter().length() > 0)
			sbQuery.append(" " + rssConfig.getFilter() + " ");
			
		sbQuery.append(" ORDER BY pubDate DESC, lastModifiedDate DESC");
		
		SQLQuery query = session.createSQLQuery(sbQuery.toString());
		query.addScalar("contentId", Hibernate.STRING);
		query.addScalar("title", Hibernate.STRING);
		query.addScalar("description", Hibernate.STRING);
		if (rssConfig.getLinkFieldId() != null && rssConfig.getLinkFieldId().length() > 0)
			query.addScalar("link", Hibernate.STRING);
		query.addScalar("pubDate", Hibernate.TIMESTAMP);
		query.setString("serviceInstanceId", rssConfig.getServInstanceId());
		query.setString("principalId", GroupConfig.GROUP_ALL_USERS_ID);
		query.setString("locale", rssConfig.getLocale());
		query.setFetchSize(rssConfig.getMaxRows());
		query.setMaxResults(rssConfig.getMaxRows());
		
		ScrollableResults results = query.scroll();
		try
		{
			while (results.next())
			{
				RssResultRow curRow = new RssResultRow();
				
				int index = 0;
				curRow.setContentId(results.getString(index++));
				curRow.setTitle(results.getString(index++));
				curRow.setDescription(results.getString(index++));

				if (rssConfig.getLinkFieldId() != null && rssConfig.getLinkFieldId().length() > 0)
					curRow.setLink(results.getString(index++));
				
				Date pubDate = results.getDate(index++);
				if(pubDate == null)
					pubDate = results.getDate(index++);
				curRow.setPublishDate(pubDate);

				rssResults.add(curRow);
			}
		}
		finally
		{
			results.close();
		}
		
		return rssResults;
	}
	
	public boolean validateUniqueFilePath(String filePath, String notIncludingRssId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			
			PreparedStatement statement = connection.prepareStatement("SELECT id FROM lum_Rss WHERE path = ? and id <> ?");
			try
			{
				statement.setString(1, filePath);
				statement.setString(2, notIncludingRssId);
				statement.setMaxRows(1);
				
				ResultSet resultSet = statement.executeQuery();
				boolean returnValue = false;
				try
				{
					returnValue = resultSet.next();
				}
				finally
				{
					resultSet.close();
				}
	
				return returnValue;
			}
			finally
			{
				statement.close();
			}
		}
		catch (DaoException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}
}
